package com.aiflow.Action;
import global.Constants;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;

import util.StringUtils;

import com.aiflow.bean.Role;
import com.aiflow.bean.User;
import com.aiflow.db.ConnDB;
import com.aiflow.util.MD5;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class Action {
	
	/** 返回用户信息表
	 *  t_u_user_Info 用户信息表	 
     *  @return返回操作结果, json	
     */
	public void returnUserInfor(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		int index = 0;
		try {
			String sql = "select name,password,remark,Login_name,mobile,email from dbo.t_u_user_Info";
			rs = db.returnRs(sql);
			try {
				while (rs.next()) {
					++index;
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("name", rs.getString(1));
					mapdata.put("password", rs.getString(2));
					mapdata.put("remark", rs.getString(3));
					mapdata.put("Login_name", rs.getString(4));
					mapdata.put("mobile", rs.getString(5));
					mapdata.put("email", rs.getString(6));
					datalist.add(mapdata);
				}
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				json.put("results", index);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	db.closeRs(rs);
				db.closeStmt();
            	db.closeConn();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	
	/** 返回分页的用户表
	 *  
	 *  @param start 起始页面
	 *  @param limit 最小值	 
	 *  t_u_user_Info 用户信息表	 
     *  @return返回操作结果, json	
     */
	public void returnUserInforPagesize(){ 
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start")); 
		int limit = Integer.parseInt(req.getParameter("limit"));  
		int end = start + limit; 
		int begin = start + 1;
		String whereSql ="";
		String fuzzy=""; 
		String dept="";
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
	    fuzzy=(String)req.getParameter("name");
		dept=(String)req.getParameter("dept");
		if(fuzzy != null && !fuzzy.equals("")){
			whereSql += " and name like '%"+fuzzy+"%'";
		}
		if(dept != null && !dept.equals("")){
			whereSql += " and dept_id = '"+dept+"'";
		}
		try {
			String sql=      //  u.dept_id=d.id 
				   "select * from "+ 
				    "("+ 
				    "select u.id,role_id,role_name,dept_id,dept_name ,name,password,Login_name,mobile,email, "+ 
				    "(row_number() over(ORDER BY id)) rb "+ 
				    "from t_u_user_Info u  where 1=1 "+whereSql+ 
				    ") tt " + 
				    "where tt.rb between "+ begin+" and "+end;    // 关键就在这里，前台的两个参数是用到SQL分页语句中，从而实现了分页查询与前台的分页显示的结合 
				 
				   ps = con.prepareStatement(sql);
			       rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("role_id", rs.getString(2));
					mapdata.put("role_name", rs.getString(3));
					mapdata.put("dept_id", rs.getString(4));
					mapdata.put("dept_name", rs.getString(5));
					mapdata.put("name", rs.getString(6));
					mapdata.put("password", rs.getString(7));
					mapdata.put("Login_name", rs.getString(8));
					mapdata.put("mobile", rs.getString(9));
					mapdata.put("email", rs.getString(10));
					
					datalist.add(mapdata);
				}
				//获取总记录条数
				String sql_count = "select count(*) as rcount from t_u_user_Info where 1=1 "+whereSql;
				int index = db.GetInfoCount(sql_count);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				json.put("results", index);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	//返回分页的角色表
	public void returnRoleInforPagesize(){ 
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start")); 
		int limit = Integer.parseInt(req.getParameter("limit"));  
		int end = start + limit; 
		int begin = start + 1;
		String fuzzy=""; 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql= 
				   "select * from "+ 
				    "("+ 
				    "select role_id,role_name,role_describe, "+ 
				    "(row_number() over(ORDER BY role_id)) rb "+ 
				    "from t_u_role_Infor "+ 
				    ") tt " + 
				    "where tt.rb between "+ begin+" and "+end;    // 关键就在这里，前台的两个参数是用到SQL分页语句中，从而实现了分页查询与前台的分页显示的结合 
				   //System.out.print("==============默认SQL ===============>>>>\n"+sql); 
				   Object obj=req.getParameter("role_name"); 
				   if(obj!=null) 
				   { 
				    fuzzy=obj.toString(); 
				    sql= "select * from "+ 
				    "("+ 
				    "select role_id,role_name,role_describe, "+ 
				    "(row_number() over(ORDER BY role_id)) rb "+ 
				    "from t_u_role_Infor where role_name like '"+fuzzy+"%' "+ 
				    ") tt " + 
				    "where tt.rb between "+ begin+" and "+end;    // 关键就在这里，前台的两个参数是用到SQL分页语句中，从而实现了分页查询与前台的分页显示的结合 
				    //System.out.print("===============分页SQL ===============>>>>\n"+sql); 
				   } 
				   ps = con.prepareStatement(sql);
			       rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("role_id", rs.getString(1));
					mapdata.put("role_name", rs.getString(2));
					mapdata.put("role_describe", rs.getString(3));
					datalist.add(mapdata);
				}
				//获取总记录条数
				String sql_count = "select count(*) as rcount from t_u_role_Infor";
				int index = db.GetInfoCount(sql_count);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				json.put("results", index);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	//注册前台用户信息
	public void registerFrontUserInfor(){ 
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String login_acct = ServletActionContext.getRequest().getParameter("login_acct");
		String name = ServletActionContext.getRequest().getParameter("name");
		String login_pass = ServletActionContext.getRequest().getParameter("login_pass");
		login_pass = MD5.encryptMD5(login_pass);
		String busi_pass = MD5.encryptMD5("123456");
		String phone_num = ServletActionContext.getRequest().getParameter("phone_num");
		String address = ServletActionContext.getRequest().getParameter("address");
		String store_name = ServletActionContext.getRequest().getParameter("store_name");
		int vest_clerk_id = Integer.parseInt(ServletActionContext.getRequest().getParameter("vest_clerk_id"));
		String area = ServletActionContext.getRequest().getParameter("area");
		Connection con = db.getConnection();
		try {
			String sql = "insert into agent_t(login_acct,login_pass,name,store_name,address,phone_num,vest_clerk_id,area,state,role_id,busi_pass) values(?,?,?,?,?,?,?,?,?,?,?)";
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, login_acct);
				ps.setString(++index, login_pass);
				ps.setString(++index, name);
				ps.setString(++index, store_name);
				ps.setString(++index, address);
				ps.setString(++index, phone_num);
				ps.setInt(++index, vest_clerk_id);
				ps.setString(++index, area);
				ps.setInt(++index, 1);
				ps.setInt(++index, 5);
				ps.setString(++index, busi_pass);
				int rs = ps.executeUpdate();
				if(rs > 0){
			//		System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            { 
            	ps.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//注册用户角色信息
	public void registerRoleInfor(){ 
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String role_name = ServletActionContext.getRequest().getParameter("role_name");
		String role_describe = ServletActionContext.getRequest().getParameter("role_describe");
		Connection con = db.getConnection();
		try {
			String sql = "insert into t_u_role_Infor(role_name,role_describe) values(?,?)";
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, role_name);
				ps.setString(++index, role_describe);
				int rs = ps.executeUpdate();
				if(rs > 0){
			//		System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            { 
            	ps.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//删除用户信息
	public void deleteUserInfor(){
		ConnDB db = new ConnDB();
		String userIds = ServletActionContext.getRequest().getParameter("userIds");
		Connection con =  db.getConnection();
		String	sql = "delete from t_u_user_Info where id in("+userIds+"0)";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
		//		System.out.println("删除成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//删除角色信息
	public void deleteRoleInfor(){
		ConnDB db = new ConnDB();
		String roleIds = ServletActionContext.getRequest().getParameter("roleIds");
		Connection con =  db.getConnection();
		String	sql = "delete from t_u_role_Infor where role_id in("+roleIds+"0)";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
		//		System.out.println("删除成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//获取用户的ID
	public void getUserID(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		String userId = ServletActionContext.getRequest().getParameter("userId");
		User user = null;
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select id,login_acct,login_pass,name,role_id,dept_id,state,add_time,expired_time from user_t where id = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1,userId);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					user = new User();
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("login_acct", rs.getString(2));
					mapdata.put("login_pass", rs.getString(3));
					mapdata.put("name", rs.getString(4));
					mapdata.put("role_id", rs.getString(5));
					mapdata.put("dept_id", rs.getString(6));
					mapdata.put("state", rs.getString(7));
					mapdata.put("add_time", rs.getString(8));
					mapdata.put("expired_time", rs.getString(9));
					datalist.add(mapdata);
					user.setId(rs.getString(1));
					user.setLogin_acct(rs.getString(2));
					user.setLogin_pass(rs.getString(3));
					user.setName(rs.getString(4));
					user.setRole_id(rs.getString(5));
					user.setDept_id(rs.getString(6));
					user.setState(rs.getString(7));
					user.setAdd_time(rs.getString(8));
					user.setExpired_time(rs.getString(9));
				}
				JSONObject json = JSONObject.fromObject(user);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//获取角色的ID
	public void getRoleID(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		String roleId = ServletActionContext.getRequest().getParameter("roleId");
		Role role = null;
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select role_id,role_name,role_describe from t_u_role_Infor where role_id = '"+roleId+"'";
			ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					role = new Role();
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("role_id", rs.getString(1));
					mapdata.put("role_name", rs.getString(2));
					mapdata.put("role_describe", rs.getString(3));
					datalist.add(mapdata);
					role.setRole_id(rs.getString(1));
					role.setRole_name(rs.getString(2));
					role.setRole_describe(rs.getString(3));
				}
				JSONObject json = JSONObject.fromObject(role);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//修改用户的信息
	public void updateUserInfor(){
		ConnDB db = new ConnDB();
		String id = ServletActionContext.getRequest().getParameter("id");
		String roleid = ServletActionContext.getRequest().getParameter("checkid");
		String rolename = ServletActionContext.getRequest().getParameter("rolename");
		String deptid = ServletActionContext.getRequest().getParameter("deptid");
		String deptname = ServletActionContext.getRequest().getParameter("deptname");
		String name = ServletActionContext.getRequest().getParameter("name");
		String Login_name = ServletActionContext.getRequest().getParameter("userName");
		String sex = ServletActionContext.getRequest().getParameter("sex");
		String birthday = ServletActionContext.getRequest().getParameter("birthday");
		String mobile = ServletActionContext.getRequest().getParameter("mobile");
		String email = ServletActionContext.getRequest().getParameter("email");
		Connection con =  db.getConnection();
		String sql = "";
		if(("".equals(roleid)||roleid == null) && "".equals(deptid)||deptid == null){
			sql = "update t_u_user_Info set name='"+name+"',Login_name='"+Login_name+"',sex='"+sex+"',birthday='"+birthday+"',mobile='"+mobile+"',email='"+email+"' where id="+id;
		}else if("".equals(roleid)){
			sql = "update t_u_user_Info set dept_id='"+deptid+"',dept_name='"+deptname+"',name='"+name+"',Login_name='"+Login_name+"',sex='"+sex+"',birthday='"+birthday+"',mobile='"+mobile+"',email='"+email+"' where id="+id;
		}else if("".equals(deptid)){
			sql = "update t_u_user_Info set role_id='"+roleid+"',role_name='"+rolename+"',name='"+name+"',Login_name='"+Login_name+"',sex='"+sex+"',birthday='"+birthday+"',mobile='"+mobile+"',email='"+email+"' where id="+id;
		}else{
			sql = "update t_u_user_Info set role_id='"+roleid+"',role_name='"+rolename+"',dept_id='"+deptid+"',dept_name='"+deptname+"',name='"+name+"',Login_name='"+Login_name+"',sex='"+sex+"',birthday='"+birthday+"',mobile='"+mobile+"',email='"+email+"' where id="+id;
		}
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
		//		System.out.println("修改成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//修改角色的信息
	public void updateRoleInfor(){
		ConnDB db = new ConnDB();
		String role_id = ServletActionContext.getRequest().getParameter("role_id");
		String role_name = ServletActionContext.getRequest().getParameter("role_name");
		String role_describe = ServletActionContext.getRequest().getParameter("role_describe");
		Connection con =  db.getConnection();
		String sql = "update t_u_role_Infor set role_name='"+role_name+"',role_describe='"+role_describe+"' where role_id="+role_id;
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
			//	System.out.println("修改成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//获取角色
	public void getRole(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONArray datas = new JSONArray();
		String id = null;
		String user_id = ServletActionContext.getRequest().getParameter("userId");
		//获取用户已有角色的ID
		List<User> listids = db.GetUserRole(user_id);
		//因为这里只能一个用户一个用户的修改，所以List返回值都是一条记录
		User user = null;
		if(listids.size() > 0){
			user = listids.get(0);
		}
		if("-1".equals(ServletActionContext.getRequest().getParameter("parentId"))){
             id = "0";
		}
		else
		{
			id = ServletActionContext.getRequest().getParameter("parentId");
		}
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select role_id,role_name,leaf,pid from dbo.t_u_role_Infor where pid = '"+id+"'";
			ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					JSONObject allAlbum = new JSONObject();
					allAlbum.put("id", Integer.parseInt(rs.getString(1)));
					allAlbum.put("text", rs.getString(2));
					//如果是0就是父节点
					if("0".equals(rs.getString(3))){
						allAlbum.put("leaf", false);
				    //如果是1就是子节点
					}else if("1".equals(rs.getString(3))){
						allAlbum.put("leaf", true);
					}
					//如果包含那就是已有的权限
					if(user != null){
						if(user.getRole_id().contains(rs.getString(1))){
							allAlbum.put("checked", true);
						}else{
							allAlbum.put("checked", false);
						}
					}else{
						allAlbum.put("checked", false);
					}
					allAlbum.put("pid", Integer.parseInt(rs.getString(4)));
					datas.add(allAlbum);
				}
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(datas.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//获取权限
	public void getPermission(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONArray datas = new JSONArray();
		String id = null;
		String role_id = ServletActionContext.getRequest().getParameter("roleId");
		//获取角色已有权限的ID
		List<String> listids = db.GetRolePer(role_id);
		if("-1".equals(ServletActionContext.getRequest().getParameter("parentId"))){
             id = "0";
		}
		else
		{
			id = ServletActionContext.getRequest().getParameter("parentId");
		}
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
          //String sql = "select id,name,leaf,pid,url from t_u_permission where pid = "+id;
			String sql = "select id,name,leaf,pid,url from dbo.t_u_permission a "+
						 "left join (select per_id from t_u_per_role where role_id = '"+role_id+"') b on a.id = b.per_id "+
						 "where pid = "+id;
			ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					JSONObject allAlbum = new JSONObject();
					allAlbum.put("id", Integer.parseInt(rs.getString(1)));
					allAlbum.put("text", rs.getString(2));
					//如果是0就是父节点
					if("0".equals(rs.getString(3))){
						allAlbum.put("leaf", false);
				    //如果是1就是子节点
					}else if("1".equals(rs.getString(3))){
						allAlbum.put("leaf", true);
					}
					//如果包含那就是已有的权限
					if(listids.contains(rs.getString(1))){
						allAlbum.put("checked", true);
					}else{
						allAlbum.put("checked", false);
					}
					allAlbum.put("pid", Integer.parseInt(rs.getString(4)));
					datas.add(allAlbum);
				}
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(datas.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//用户新增角色
	public void newRole(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONArray datas = new JSONArray();
		String id = null;
		if("-1".equals(ServletActionContext.getRequest().getParameter("parentId"))){
             id = "0";
		}
		else
		{
			id = ServletActionContext.getRequest().getParameter("parentId");
		}
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
            String sql = "select role_id,role_name,leaf,pid from t_u_role_Infor where pid = "+id;
			ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					JSONObject allAlbum = new JSONObject();
					allAlbum.put("id", Integer.parseInt(rs.getString(1)));
					allAlbum.put("text", rs.getString(2));
					//如果是0就是父节点
					if("0".equals(rs.getString(3))){
						allAlbum.put("leaf", false);
				    //如果是1就是子节点
					}else if("1".equals(rs.getString(3))){
						allAlbum.put("leaf", true);
					}
					//这里是新增的,所以默认都是false
				    allAlbum.put("checked", false);
					allAlbum.put("pid", Integer.parseInt(rs.getString(4)));
					datas.add(allAlbum);
				}
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(datas.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//用户新增权限
	public void newPermission(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONArray datas = new JSONArray();
		String id = null;
		if("-1".equals(ServletActionContext.getRequest().getParameter("parentId"))){
             id = "0";
		}
		else
		{
			id = ServletActionContext.getRequest().getParameter("parentId");
		}
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
            String sql = "select id,name,leaf,pid,url from t_u_permission where pid = "+id;
			ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					JSONObject allAlbum = new JSONObject();
					allAlbum.put("id", Integer.parseInt(rs.getString(1)));
					allAlbum.put("text", rs.getString(2));
					//如果是0就是父节点
					if("0".equals(rs.getString(3))){
						allAlbum.put("leaf", false);
				    //如果是1就是子节点
					}else if("1".equals(rs.getString(3))){
						allAlbum.put("leaf", true);
					}
					//这里是新增的,所以默认都是false
				    allAlbum.put("checked", false);
					allAlbum.put("pid", Integer.parseInt(rs.getString(4)));
					datas.add(allAlbum);
				}
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(datas.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//修改角色的权限
	public void updatePerInfor(){
		ConnDB db = new ConnDB();
		String role_id = ServletActionContext.getRequest().getParameter("roleId");
		//当前角色用户的所有权限的数组
		String perids = ServletActionContext.getRequest().getParameter("per_id");
		String[] per_ids = perids.split(",");
		Connection con =  db.getConnection();
		String sql = "insert into t_u_per_role(role_id,per_id) values(?,?)";
		PreparedStatement ps = null;
		try {
			//先删除当前角色的所有权限，然后在新增选中的权限
			db.deleteRolePer(role_id);
			ps = con.prepareStatement(sql);
			for (int i = 0; i < per_ids.length; i++) {
				int index = 0;
				ps.setString(++index, role_id);
				ps.setString(++index, per_ids[i]);
				ps.addBatch();
			}
			int[] rs = ps.executeBatch();
			if(rs.length > 0){
		//		System.out.println("保存成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//修改用户密码
	public void changePwd(){
		ConnDB db = new ConnDB();
		String errorMsg = "";
		boolean isSuccess = true;
		String userId = ServletActionContext.getRequest().getParameter("userId");
		String oldpassword = ServletActionContext.getRequest().getParameter("oldpassword");
	    String password = ServletActionContext.getRequest().getParameter("password");
	    String repassword = ServletActionContext.getRequest().getParameter("repassword");
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		if ((StringUtils.isEmpty(password)) || (StringUtils.isEmpty(repassword)) || (!password.equals(repassword))) {
		      isSuccess = false;
		      errorMsg = "两次输入的密码不一致.";
		}
		boolean flag = db.login(userId, MD5.encryptMD5(oldpassword));
		if (flag == false) {
		      isSuccess = false;
		      errorMsg = "原密码错误.";
		}
		if(isSuccess){
			password = MD5.encryptMD5(password);
			String sql = "update t_u_user_Info set password = '"+password+"' where Login_name = '"+userId+"'";
			try {
				ps = con.prepareStatement(sql);
				int rs = ps.executeUpdate();
				if(rs > 0){
			//		System.out.println("修改成功!!!");
				}
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		try {
			JSONObject json = new JSONObject();
			json.put("success", isSuccess);
			json.put("message", errorMsg);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				if(ps != null){
					ps.close();
				}
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//获取菜单
	public void getMenuTree(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONArray datas = new JSONArray();
		//主树
		String id = ServletActionContext.getRequest().getParameter("parentId");
		String roleid = ServletActionContext.getRequest().getParameter("roleid");
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select id,name,leaf,pid,url from privilege_t where id in " +
					     "(select privilege_id from role_privilege_t where role_id in ("+roleid+")) and pid = "+id;
			ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					JSONObject allAlbum = new JSONObject();
					allAlbum.put("id", Integer.parseInt(rs.getString(1)));
					allAlbum.put("text", rs.getString(2));
					//如果是0就是父节点
					if("0".equals(rs.getString(3))){
						allAlbum.put("leaf", false);
				    //如果是1就是子节点
					}else if("1".equals(rs.getString(3))){
						allAlbum.put("leaf", true);
					}
					allAlbum.put("pid", Integer.parseInt(rs.getString(4)));
					datas.add(allAlbum);
				}
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(datas.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//通讯录的分页
	public void returnPhoneList() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start")); 
		int limit = Integer.parseInt(req.getParameter("limit"));  
		int end = start + limit; 
		int begin = start + 1;
		String fuzzy=""; 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		fuzzy=(String)req.getParameter("name");
		if(fuzzy != null && !fuzzy.equals("")){
			whereSql += " and name like '%"+fuzzy+"%'";
		}
		Connection con = db.getConnection();
		PreparedStatement ps = null;

		try {
			String sql=
				   "select * from "+ 
		           "("+ 
				   "select id,phoneid,name,phone,addTime,(row_number() over(ORDER BY id desc)) rb  from  t_s_phonelist  where 1=1 "+whereSql+ 
				    ") tt " + 
				    "where tt.rb between "+ begin+" and "+end;    // 关键就在这里，前台的两个参数是用到SQL分页语句中，从而实现了分页查询与前台的分页显示的结合 

			String sql_count = "select count(1) as rcount from t_s_phonelist where 1=1 "+whereSql;

			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("phoneid", rs.getString(2));
				mapdata.put("name", rs.getString(3));
				mapdata.put("phone", rs.getString(4));
				mapdata.put("addTime", rs.getString(5));
				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sql_count);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();

			}
		}
	}
	
	//获取所有通讯录组名称
	public void returnPhonelistName(){ 
			ConnDB db = new ConnDB();
			ResultSet rs = null;
			JSONObject json = new JSONObject();
			List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
			JSONArray datas = new JSONArray();
			int index = 0;
			try {
				String sql = "select id,name from dbo.dictionary where pid = 702";
				rs = db.returnRs(sql);
				try {
					while (rs.next()) {
						++index;
						Map<String,String> mapdata = new HashMap<String,String>();
						mapdata.put("id", rs.getString(1));
						mapdata.put("name", rs.getString(2));
						mapdata.put("id_name", rs.getString(1)+" "+rs.getString(2));
						datalist.add(mapdata);
					}
					datas = JSONArray.fromObject(datalist);
					json.put("success", true);
					json.put("datas", datas);
					json.put("results", index);
					HttpServletResponse response = ServletActionContext.getResponse();     
					response.setCharacterEncoding("UTF-8");  
					PrintWriter out = response.getWriter();
					out.print(json);
					out.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally
	        {
	            try
	            {
	            	db.closeRs(rs);
					db.closeStmt();
	            	db.closeConn();
	            }
	            catch(Exception ex1)
	            {
	                ex1.printStackTrace();
	            }
	        }
	}
	
	//添加通讯录信息
	public void insertPhoneInfor(){ 
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String address = ServletActionContext.getRequest().getParameter("address");
		String phone = ServletActionContext.getRequest().getParameter("phone");
		Connection con = db.getConnection();
		String addre[] = address.split("\\s{1,}");
		String phoneid = addre[0];
		String name = addre[1];
		try {
			String sql = "insert into t_s_phonelist(phoneid,name,phone) values(?,?,?)";
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, phoneid);
				ps.setString(++index, name);
				ps.setString(++index, phone);
				int rs = ps.executeUpdate();
				if(rs > 0){
			//		System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            { 
            	ps.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//修改通讯录组的信息
	public void updatePhoneListInfor(){
		ConnDB db = new ConnDB();
		String id = ServletActionContext.getRequest().getParameter("id");
		String phone = ServletActionContext.getRequest().getParameter("phone");
		Connection con =  db.getConnection();
		String sql = "update t_s_phonelist set phone = '"+phone+"' where id = "+id;
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
		//		System.out.println("修改成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//删除通讯录组信息
	public void deletePhoneListInfor(){
		ConnDB db = new ConnDB();
		String id = ServletActionContext.getRequest().getParameter("addressIds");
		Connection con =  db.getConnection();
		String	sql = "delete from t_s_phonelist where id in("+id+"0)";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
		//		System.out.println("删除成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//获取通讯录
	public void getAddress(){ 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		Connection con =  db.getConnection();
		JSONObject json = new JSONObject();
		PreparedStatement ps = null;
		try {
			String sql = "select id,phoneid,name,phone from dbo.t_s_phonelist";
			ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("phoneid", rs.getString(2));
					mapdata.put("name", rs.getString(3));
					mapdata.put("phone", rs.getString(4));
					datalist.add(mapdata);
				}
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	/** 返回分页的告警标题配置表
	 *  
	 *  @param start 起始页面
	 *  @param limit 最小值	 
	 *  Alarm_pro 告警标题信息表	 
     *  @return返回操作结果, json	
     */
	public void returnAlarmpro(){ 
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start")); 
		int limit = Integer.parseInt(req.getParameter("limit"));  
		int end = start + limit; 
		int begin = start + 1;
		String whereSql ="";
		String fuzzy=""; 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
	    fuzzy=(String)req.getParameter("alarm_title");
		if(fuzzy != null && !fuzzy.equals("")){
			whereSql += " and alarm_title like '%"+fuzzy+"%'";
		}
		try {
			String sql = 
				"select * from "+ 
			    "("+ 
			    "select u.id,alarm_type,b.name,alarm_title,"+ 
			    "(row_number() over(ORDER BY u.id)) rb "+ 
			    "from Alarm_pro u left join dictionary b on u.alarm_type=b.id where 1=1 "+whereSql+ 
			    ") tt " + 
			    "where tt.rb between "+ begin+" and "+end;
				   ps = con.prepareStatement(sql);
			       rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("alarm_type", rs.getString(2));
					mapdata.put("name", rs.getString(3));
					mapdata.put("alarm_title", rs.getString(4));
					datalist.add(mapdata);
				}
				//获取总记录条数
				String sql_count = "select count(*) as rcount from Alarm_pro where 1=1 "+whereSql;
				int index = db.GetInfoCount(sql_count);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				json.put("results", index);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	//添加告警标题信息
	public void insertAlarmproInfor(){ 
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String alarmType = ServletActionContext.getRequest().getParameter("alarm_type");
		String configType = ServletActionContext.getRequest().getParameter("config_type");
		String alarmTitle = ServletActionContext.getRequest().getParameter("alarm_title");
		//在数据库中是否存在一样的告警类型和告警标题
		boolean flag = db.valiAlarmpro(alarmType,alarmType);
		JSONObject json = new JSONObject();
		Connection con = null;
		String errorMsg = "";
		try {
		if(flag){
			errorMsg = "已存在相同的告警类型和告警标题!!!";
			json.put("success", false);
			json.put("message", errorMsg);
		}else{
			con = db.getConnection();
			String sql = "insert into Alarm_pro(alarm_type,config_type,alarm_title) values(?,?,?)";
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, alarmType);
				ps.setString(++index, configType);
				ps.setString(++index, alarmTitle);
				int rs = ps.executeUpdate();
				if(rs > 0){
					//		System.out.println("保存成功!!!");
				}
				json.put("success", true);
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		HttpServletResponse response = ServletActionContext.getResponse();  
		response.setCharacterEncoding("UTF-8");  
		PrintWriter out = response.getWriter();
		out.print(json);
		out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
        {
            try
            { 
            	if(ps != null){
            		ps.close();
            		con.close();
            	}
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	//删除告警标题信息
	public void deleteAlarmTitleInfor(){
		ConnDB db = new ConnDB();
		String userIds = ServletActionContext.getRequest().getParameter("userIds");
		Connection con =  db.getConnection();
		String	sql = "delete from Alarm_pro where id in("+userIds+"0)";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
		//		System.out.println("删除成功!!!");
			}
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	// 获取选中告警标题
	public void getAlarmproCon() {
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String UserConfigId = ServletActionContext.getRequest().getParameter("userConfId");
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select id,alarm_type,alarm_title,config_type from Alarm_pro u where u.id = "+ UserConfigId;

			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			Map<String, String> mapdata = new HashMap<String, String>();
			try {
				while (rs.next()) {
					mapdata.put("id", rs.getString(1));
					mapdata.put("alarm_type", rs.getString(2));
					mapdata.put("alarm_title", rs.getString(3));
					mapdata.put("config_type", rs.getString(4));
				}
				JSONObject json = JSONObject.fromObject(mapdata);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 修改告警标题
	public void updateAlarmpro() {
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String id = ServletActionContext.getRequest().getParameter("id");
		String alarmType = ServletActionContext.getRequest().getParameter("alarm_type");
		String alarmTitle = ServletActionContext.getRequest().getParameter("alarm_title");
		String configType = ServletActionContext.getRequest().getParameter("config_type");
		String sql = "update Alarm_pro set alarm_type = ?,alarm_title = ?,config_type = ? where id = ?";
		Connection con = db.getConnection();
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, alarmType);
				ps.setString(++index, alarmTitle);
				ps.setString(++index, configType);
				ps.setString(++index, id);
				int rs = ps.executeUpdate();
//				if (rs > 0) {
//					System.out.println("保存成功!!!");
//				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						ps.close();
						con.close();
					} catch (Exception ex1) {
						ex1.printStackTrace();
					}
				}
	}
	
	// 获取选中告警拨打记录
	public void getAlarmQuery() {
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		String alarmIds = ServletActionContext.getRequest().getParameter("alarmIds");
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select id,snumbe,tel,calltime,answertime,disposition,endtime from dbo.t_s_alarmRecord where snumbe = '"+alarmIds+"'";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String, String> mapdata = new HashMap<String, String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("snumbe", rs.getString(2));
					mapdata.put("tel", rs.getString(3));
					mapdata.put("calltime", rs.getString(4));
					mapdata.put("answertime", rs.getString(5));
					mapdata.put("disposition", rs.getString(6));
					mapdata.put("endtime", rs.getString(7));
					datalist.add(mapdata);
				}
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 查询 督办工单信息
	public void findHandle() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		String name = (String) req.getParameter("name");
		int end = start + limit;
		int begin = start + 1;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if(name != null && !name.equals("")){
			whereSql = " and  d.name like '%"+name+"%' ";
		}
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select * from "
					+ "("
					+ "select h.id,d.name as supervise_name,supervise as supervise_id,supervise_target,target_name,pattern,staTime,endTime,case when  pulse = '1' then '开启' when  pulse = '0' then '关闭' end as pulse,(row_number() over(ORDER BY h.id desc)) rb  from dbo.t_s_handle h,dictionary d where h.supervise = d.id "+whereSql+") tt "
					+ "where tt.rb between " + begin + " and " + end;

			sqlCount = "select h.id  from dbo.t_s_handle h,dictionary d where h.supervise = d.id ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("supervise_name", rs.getString(2));
				mapdata.put("supervise_id", rs.getString(3));
				mapdata.put("supervise_target", rs.getString(4));
				mapdata.put("target_name", rs.getString(5));
				mapdata.put("pattern", rs.getString(6));
				mapdata.put("staTime", rs.getString(7));
				mapdata.put("endTime", rs.getString(8)); // ,pattern,staTime,endTime
				mapdata.put("pulse", rs.getString(9));
				datalist.add(mapdata);
			}
			int index = db.GetDataCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 新增督办工单
	public void addHandle() {
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String supervise_id = ServletActionContext.getRequest().getParameter(
				"supervise_id");
		String targetName = ServletActionContext.getRequest().getParameter(
				"targetName");
		String superviseTargetId = ServletActionContext.getRequest().getParameter(
				"checkid");
		String startTime = ServletActionContext.getRequest().getParameter(
				"startTime");
		String endTime = ServletActionContext.getRequest().getParameter(
				"endTime");

		String pattern = ServletActionContext.getRequest().getParameter(
				"pattern");
		
		String pulse = ServletActionContext.getRequest().getParameter(
		"pulse");

		Connection con = db.getConnection(); // checkid
		try {
			String sql = "insert into dbo.t_s_handle(supervise,supervise_target,target_name,staTime,endTime,pattern,pulse) values(?,?,?,?,?,?,?)";
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, supervise_id);
				ps.setString(++index, superviseTargetId);
				ps.setString(++index, targetName);
				ps.setString(++index, startTime);
				ps.setString(++index, endTime);
				ps.setString(++index, pattern);
				ps.setString(++index, pulse);
				int rs = ps.executeUpdate();
				if (rs > 0) {
				//	System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 删除 督办工单
	public void deleteHandle() {
		ConnDB db = new ConnDB();
		Statement ps = null;
		HttpServletRequest req = ServletActionContext.getRequest();
		String id = req.getParameter("handleIds");
		id = id.substring(0, id.length() - 1);
		Connection con = db.getConnection();
		try {
			con.setAutoCommit(false);
			ps = con.createStatement();
			String sql = "delete t_s_handle  where id in( " + id + " )";
			ps.execute(sql);
			ps = con.createStatement();
			JSONObject json = new JSONObject();
			json.put("success", true);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {
				con.commit();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 获取选中督办工单的对象
	public void getHandle() {
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String para = ServletActionContext.getRequest()
				.getParameter("handleId");
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select h.id,d.name,supervise,supervise_target,target_name,statime,endtime,pattern,pulse   from dbo.t_s_handle h,dictionary d where h.supervise = d.id and h.id = "
					+ para;

			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			Map<String, String> mapdata = new HashMap<String, String>();
			try {
				while (rs.next()) {

					mapdata.put("id", rs.getString(1));
					mapdata.put("supervise_name", rs.getString(2));
					mapdata.put("supervise_id", rs.getString(3));
					mapdata.put("supervise_target", rs.getString(4));
					mapdata.put("target_name", rs.getString(5));
					mapdata.put("heraldstaTime", rs.getString(6));
					mapdata.put("heraldendTime", rs.getString(7));
					mapdata.put("pattern", rs.getString(8));
					mapdata.put("pulse", rs.getString(9));

				}
				JSONObject json = JSONObject.fromObject(mapdata);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext
						.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 修改督办工单
	public void updateHandle() {
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String alarm_id = ServletActionContext.getRequest().getParameter(
				"supervise_id");
		String checkid = ServletActionContext.getRequest().getParameter(
				"checkid");
		String targetName = ServletActionContext.getRequest().getParameter(
				"targetName");
		String staTime = ServletActionContext.getRequest().getParameter(
				"staTime");
		String endTime = ServletActionContext.getRequest().getParameter(
				"endTime");
		String pattern = ServletActionContext.getRequest().getParameter(
				"pattern");
		String id = ServletActionContext.getRequest().getParameter("id");

		String sql = "";
		Connection con = db.getConnection();
		try {
			if (checkid != null && !checkid.equals("-1")) {
				sql = "update t_s_handle set supervise = ?,  supervise_target = ? ,target_name =?,staTime = ?,endTime=?,pattern=?   where id = ?";
			} else {
				sql = "update t_s_handle set supervise = ? ,staTime = ?,endTime=?,pattern=?   where id = ?";
			}

			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, alarm_id);
				if (checkid != null && !checkid.equals("-1")) {
					ps.setString(++index, checkid);
					ps.setString(++index, targetName);
				}
				ps.setString(++index, staTime);
				ps.setString(++index, endTime);
				ps.setString(++index, pattern);
				ps.setString(++index, id);
				int rs = ps.executeUpdate();
				if (rs > 0) {
			//		System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext
						.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 查询督办人员信息
	public void findPersonConfig() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		String name = (String) req.getParameter("userName");
		String dept = (String) req.getParameter("dept");
		int end = start + limit;
		int begin = start + 1;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if(name != null && !name.equals("")){
			whereSql = " and  u.user_name like '%"+name+"%' ";
		}
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;

		try {
			String sql = "select * from "
					+ "("
					+ "select u.id,u.user_name as userName,u.telep ,d.name as targetName,u.superviseTarget,dept.dept_name,dspe.name as spec, "+
					  " (row_number() over(ORDER BY u.id desc)) rb "+
					  " from t_s_personConfigure u,dictionary d ,dictionary dspe, department dept where d.id = u.superviseTarget "+ 
					  " and dspe.id = u.specialty and dept.id = u.region and dept.dept_name = '"+dept+"' "+whereSql+") tt "
					+ "where tt.rb between " + begin + " and " + end;

			sqlCount = "select count(1) "+
					  " from t_s_personConfigure u,dictionary d ,dictionary dspe, department dept where d.id = u.superviseTarget "+ 
					  " and dspe.id = u.specialty and dept.id = u.region and dept.dept_name = '"+dept+"' "+whereSql;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("userName", rs.getString(2));
				mapdata.put("telep", rs.getString(3));
				mapdata.put("targetName", rs.getString(4));
				mapdata.put("superviseTarget", rs.getString(5));
				mapdata.put("dept_name", rs.getString(6));
				mapdata.put("spec", rs.getString(7));
				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();

			}
		}

	}
	
	// 修改督办人员
	public void updatePersonConfig() {
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String id = ServletActionContext.getRequest().getParameter(
				"id");
		String user_name = ServletActionContext.getRequest().getParameter(
				"user_name");
		String telep = ServletActionContext.getRequest().getParameter(
				"telep");
		String superviseTarget = ServletActionContext.getRequest().getParameter(
				"superviseTarget");
		String region = ServletActionContext.getRequest().getParameter(
				"region");
		String specialty = ServletActionContext.getRequest().getParameter(
				"specialty");
		String describe = ServletActionContext.getRequest().getParameter("describe");
		String sql = "update t_s_personConfigure set user_name = ?,telep= ? ,superviseTarget =?,region= ?,specialty=?,describe=? where id = ?";
		Connection con = db.getConnection();
			try {
				ps = con.prepareStatement(sql);
				ps.setString(1, user_name);
				ps.setString(2, telep);
				ps.setString(3, superviseTarget);
				ps.setString(4, region);
				ps.setString(5, specialty);
				ps.setString(6, describe);
				ps.setString(7, id);
				int rs = ps.executeUpdate();
				if (rs > 0) {
					// System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						ps.close();
						con.close();
					} catch (Exception ex1) {
						ex1.printStackTrace();
					}
				}
	}
	
	// 获取选中督办人员的对象
	public void getSuperviseObj() {
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		String sortIds = ServletActionContext.getRequest().getParameter("sortIds");
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select id,supervise,supervise_target,target_name from t_s_handle where id = "+ sortIds;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			try {
				while (rs.next()) {
					if(rs.getString(3).contains(",")){
						String alarm[] = rs.getString(3).split(",");
						String target_name[] = rs.getString(4).split(",");
						for (int i = 0; i < alarm.length; i++) {
							Map<String, String> mapdata = new HashMap<String, String>();
							mapdata.put("id", rs.getString(1));
							mapdata.put("supervise", rs.getString(2));
							mapdata.put("supervise_target", alarm[i]);
							mapdata.put("target_name", target_name[i]);
							datalist.add(mapdata);
						}
					}else{
						Map<String, String> mapdata = new HashMap<String, String>();
						mapdata.put("id", rs.getString(1));
						mapdata.put("supervise", rs.getString(2));
						mapdata.put("supervise_target", rs.getString(3));
						mapdata.put("target_name", rs.getString(4));
						datalist.add(mapdata);
					}
				}
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 获取选中督办人员
	public void getSupervisePerson() {
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		String sortIds = ServletActionContext.getRequest().getParameter("sortIds");
		Connection con = db.getConnection();
		CallableStatement cstmt = null;
		try {
			//String sql = "select id,user_name,telep,superviseTarget,region,sort from t_s_personConfigure where id = ?";
			String sql = "{call getPserson(?)}";
			cstmt = con.prepareCall(sql);
	    	cstmt.setInt(1, Integer.parseInt(sortIds));
	    	rs = cstmt.executeQuery();
			try {
				while (rs.next()) {
					Map<String, String> mapdata = new HashMap<String, String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("user_name", rs.getString(2));
					mapdata.put("telep", rs.getString(3));
					mapdata.put("superviseTarget", rs.getString(4));
					mapdata.put("region", rs.getString(5));
					mapdata.put("sort", rs.getString(6));
					mapdata.put("targetName", rs.getString(7));
					datalist.add(mapdata);
				}
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 督办对象排序
	public void updateSuperviseId() {
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String id = ServletActionContext.getRequest().getParameter("id");
		String superviseTarget = ServletActionContext.getRequest().getParameter("superviseTarget");
		String targetName = ServletActionContext.getRequest().getParameter("targetName");
		String sql = "update t_s_handle set supervise_target = ?,target_name = ? where id = ?";
		Connection con = db.getConnection();
			try {
				ps = con.prepareStatement(sql);
				ps.setString(1, superviseTarget);
				ps.setString(2, targetName);
				ps.setString(3, id);
				int rs = ps.executeUpdate();
				if (rs > 0) {
					// System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						ps.close();
						con.close();
					} catch (Exception ex1) {
						ex1.printStackTrace();
					}
				}
	}
	
	// 督办人员排序
	public void updateSupervisePersonId() {
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String[] ids = ServletActionContext.getRequest().getParameterValues("Ids");
		String[] sorts = ServletActionContext.getRequest().getParameterValues("sorts");
		JSONObject json = new JSONObject();
		String sql = "update t_s_personConfigure set sort = ? where id = ?";
		Connection con = db.getConnection();
			try {
				ps = con.prepareStatement(sql);
				for (int i = 0; i < sorts.length; i++) {
					ps.setString(1, sorts[i]);
					ps.setString(2, ids[i]);
					ps.addBatch();
				}
				ps.executeBatch();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						ps.close();
						con.close();
					} catch (Exception ex1) {
						ex1.printStackTrace();
					}
				}
	}
	
	// 查询 督办记录
	public void findSupervise() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		String region = req.getParameter("region");
		String startTime = req.getParameter("startTime");
		String endTime = req.getParameter("endTime");
		String superviseTitle = req.getParameter("superviseTitle");
		String superviseStatus = null;
		if (startTime != null && !startTime.equals("")) {
			startTime = (startTime.split("T"))[0];
		}
		if (endTime != null && !endTime.equals("")) {
			endTime = (endTime.split("T"))[0];
		}
		if ((req.getParameter("superviseStatus")).equals("") || (req.getParameter("superviseStatus")).equals("-1")) {
			superviseStatus = "";
		}else {
			superviseStatus = req.getParameter("superviseStatus");
		}
		int end = start + limit;
		int begin = start + 1;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if (region != null && !region.equals("")) {
			if (region.equals(Constants.netManager)) {
				whereSql += " and   Region_name = '' ";
			} else {
				whereSql += " and   Region_name like '%" + region + "%' ";
			}
		}
		if (superviseStatus != null && !superviseStatus.equals("")) {
			whereSql += " and  send = '" + superviseStatus + "' ";
		}
		if (startTime != null && !startTime.equals("")) {
			whereSql += " and  Supervise_time >= '" + startTime + "' ";
		}
		if (endTime != null && !endTime.equals("")) {
			whereSql += " and  Supervise_time < '" + endTime + " 23:59:59' ";
		}
		if (superviseTitle != null && !superviseTitle.equals("")) {
			whereSql += " and  Supervise_title like '%" + superviseTitle + "%' ";
		}
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql="  select * from (" +
			        " select id,Supervise_id,Region_name, " +
					" Supervise_time,Supervise_user,work_level,work_title,work_time,wy,Supervise_status,send, " +
					" (row_number() over(ORDER BY id desc)) rb from Supervise_info  where 1=1 "+ whereSql +" )tt " +
					" where tt.rb "+
					" between " + begin + " and " + end;
			sqlCount = "select count(1) " + " from Supervise_info where 1=1 "
					+ whereSql;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("Supervise_id", rs.getString(2));
				mapdata.put("Region_name", rs.getString(3));
				mapdata.put("Supervise_time", rs.getString(4));
				mapdata.put("Supervise_user", rs.getString(5));
				mapdata.put("work_level", rs.getString(6));
				mapdata.put("work_title", rs.getString(7));
				mapdata.put("work_time", rs.getString(8));
				mapdata.put("wy", rs.getString(9));
				mapdata.put("supervise_status", rs.getString(10));
				mapdata.put("send", rs.getString(11));
				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 获取选中的督办工单拨打记录
	public void getSuperviseQuery() {
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		String superviseIds = ServletActionContext.getRequest().getParameter("superviseIds");
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select id,superviseid,tel,calltime,answertime,disposition,endtime,context,supervise_status from dbo.t_s_superviseRecord where superviseId like '%"+superviseIds+"%'";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String, String> mapdata = new HashMap<String, String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("superviseid", rs.getString(2));
					mapdata.put("tel", rs.getString(3));
					mapdata.put("calltime", rs.getString(4));
					mapdata.put("answertime", rs.getString(5));
					mapdata.put("disposition", rs.getString(6));
					mapdata.put("endtime", rs.getString(7));
					mapdata.put("context", rs.getString(8));
					mapdata.put("supervise_status", rs.getString(9));
					datalist.add(mapdata);
				}
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json.toString());
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 查询 工单语音通知信息
	public void findSuperviseRecord() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		int end = start + limit;
		int begin = start + 1;
		String region = req.getParameter("region");
		String startTime= (String) req.getParameter("startTime");
		String endTime = (String) req.getParameter("endTime");
		String backVal = (String) req.getParameter("backCom");  //返回值
		String superviseId = (String) req.getParameter("superviseId");  //返回值
		if(startTime != null && !startTime.equals("")){
		startTime = (startTime.split("T"))[0];
		}
		if(endTime != null && !endTime.equals("")){
		endTime = (endTime.split("T"))[0];
		}
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if(startTime != null && !startTime.equals("")){
		    whereSql += " and sendTime >= '"+startTime+"' ";
		 }
		if(endTime != null && !endTime.equals("")){
			whereSql += " and sendTime < '"+endTime+"' ";
		}
		if("answered".equals(backVal)){
			whereSql += " and disposition = '"+backVal+"' ";
		}else if(backVal != null && !backVal.equals("") && !"-1".equals(backVal)){
			whereSql += " and disposition <> 'answered' ";
		}
		if (region != null && !region.equals("")) {
			whereSql += " and region_name like '%" + region + "%' ";
		}
		if (superviseId != null && !superviseId.equals("")) {
			whereSql += " and superviseId like '%" + superviseId + "%' ";
		}
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select * from "
					+ "("
					+ " select a.id,superviseId,MVB_CALLER,a.user_name,tel,a.superviseTargetId,d.name,sendTime,backTime, case disposition when 'ANSWERED' then '已接听' when '' then '正在接通' else '未接听' end as disposition ,"+  
                      " (row_number() over(ORDER BY a.id desc)) rb,a.context,a.supervise_status,a.region_name  from t_s_superviseRecord a,dictionary d "+
                      " where a.superviseTargetId = d.id "+whereSql+") tt "
					+ "where tt.rb between " + begin + " and " + end;
			sqlCount = "select count(1) "+
					  " from t_s_superviseRecord a,dictionary d where a.superviseTargetId = d.id "+whereSql;   
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("superviseId", rs.getString(2));
				mapdata.put("MVB_CALLER", rs.getString(3));
				mapdata.put("userName", rs.getString(4));
				mapdata.put("tel", rs.getString(5));
				mapdata.put("deptId", rs.getString(7));
				mapdata.put("sendTime", rs.getString(8));
				mapdata.put("backTime", rs.getString(9));
				mapdata.put("disposition", rs.getString(10));
				mapdata.put("context", rs.getString(12));
				mapdata.put("supervise_status", rs.getString(13));
				mapdata.put("region_name", rs.getString(14));
				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	// 查询 工单通话流水信息
	public void findSuperviseStream() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		int end = start + limit;
		int begin = start + 1;
		String region = req.getParameter("region");
		String startTime = (String) req.getParameter("startTime");
		String endTime = (String) req.getParameter("endTime");
		String tele = (String) req.getParameter("tele"); // 返回值
		if (startTime != null && !startTime.equals("")) {
			startTime = (startTime.split("T"))[0];
		}
		if (endTime != null && !endTime.equals("")) {
			endTime = (endTime.split("T"))[0];
		}
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if (startTime != null && !startTime.equals("")) {
			whereSql += " and   sendTime >= '" + startTime + "' ";
		}
		if (endTime != null && !endTime.equals("")) {
			whereSql += " and  sendTime < '" + endTime + "' ";
		}
		if (tele != null && !tele.equals("")) {
			whereSql += " and  tel = '" + tele + "' ";
		}
		if (region != null && !region.equals("")) {
			whereSql += " and   region_name like '%" + region + "%' ";
		}
		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select * from "
					+ "("
					+ " select a.id,superviseId,MVB_CALLER,a.user_name,tel,a.superviseTargetId,d.name,sendTime, calltime,answertime ,endtime,duration,supervise_status,a.region_name,"
					+ " (row_number() over(ORDER BY a.id desc)) rb  from t_s_superviseRecord a,dictionary d "
					+ " where a.superviseTargetId = d.id and disposition = 'ANSWERED' "
					+ whereSql + ") tt " + "where tt.rb between " + begin
					+ " and " + end;
			sqlCount = "select count(1) "
					+ " from t_s_superviseRecord a,dictionary d where a.superviseTargetId = d.id  and disposition = 'ANSWERED' "
					+ whereSql;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("superviseId", rs.getString(2));
				mapdata.put("MVB_CALLER", rs.getString(3));
				mapdata.put("userName", rs.getString(4));
				mapdata.put("tel", rs.getString(5));
				mapdata.put("name", rs.getString(7));
				mapdata.put("sendTime", rs.getString(8));
				mapdata.put("calltime", rs.getString(9));
				mapdata.put("answertime", rs.getString(10));
				mapdata.put("endtime", rs.getString(11));
				mapdata.put("duration", rs.getString(12));
				mapdata.put("supervise_status", rs.getString(13));
				mapdata.put("region_name", rs.getString(14));
				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();

			}
		}
	}
	
	// 查询号段库
	public void findNum() {
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		String num = ServletActionContext.getRequest().getParameter("num");
		Connection con = db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select region,type from num_t where num = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, num);
			rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String, String> mapdata = new HashMap<String, String>();
					mapdata.put("region", rs.getString(1));
					mapdata.put("type", rs.getString(2));
					datalist.add(mapdata);
				}
				json.put("success", true);
				json.put("datas", datalist);
				HttpServletResponse response = ServletActionContext.getResponse();
				response.setCharacterEncoding("UTF-8");
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}
		}
	}
	
	//前台话费余额查询
	public void balanceQuery(){ 
		JSONObject json = new JSONObject();  
		String num = ServletActionContext.getRequest().getParameter("num");
		try {
			JSONObject jsonObject = new JSONObject();  
			jsonObject.put("balance", "125");
			json.put("success", true);
			json.put("datas", jsonObject);
			HttpServletResponse response = ServletActionContext.getResponse();     
			response.setCharacterEncoding("UTF-8");  
			PrintWriter out = response.getWriter();
			out.print(json.toString());
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//前台代理商充值话费
	public void prepaid(){ 
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String login_acct = ServletActionContext.getRequest().getParameter("num");
		String name = ServletActionContext.getRequest().getParameter("name");
		String login_pass = ServletActionContext.getRequest().getParameter("login_pass");
		login_pass = MD5.encryptMD5(login_pass);
		String busi_pass = MD5.encryptMD5("123456");
		String phone_num = ServletActionContext.getRequest().getParameter("phone_num");
		String address = ServletActionContext.getRequest().getParameter("address");
		String store_name = ServletActionContext.getRequest().getParameter("store_name");
		int vest_clerk_id = Integer.parseInt(ServletActionContext.getRequest().getParameter("vest_clerk_id"));
		String area = ServletActionContext.getRequest().getParameter("area");
		Connection con = db.getConnection();
		try {
			String sql = "insert into agent_t(login_acct,login_pass,name,store_name,address,phone_num,vest_clerk_id,area,state,role_id,busi_pass) values(?,?,?,?,?,?,?,?,?,?,?)";
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, login_acct);
				ps.setString(++index, login_pass);
				ps.setString(++index, name);
				ps.setString(++index, store_name);
				ps.setString(++index, address);
				ps.setString(++index, phone_num);
				ps.setInt(++index, vest_clerk_id);
				ps.setString(++index, area);
				ps.setInt(++index, 1);
				ps.setInt(++index, 5);
				ps.setString(++index, busi_pass);
				int rs = ps.executeUpdate();
				if(rs > 0){
			//		System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            { 
            	ps.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	public static void main(String[] args) {
		//Action action = new Action();
	}
	}
